--select * from ms_style where age_group = 'H' and size_group = 'SHOES'
--select * from ms_style where age_group = 'F' and size_group = 'SHOES'

begin tran
declare @gv_upd_id int
select @gv_upd_id = var_data from  igs_parm where var_name = 'gv_upd_id'
update ms_style set  upd_id = @gv_upd_id, size_group = 'H-SHOES'
where age_group = 'H' and size_group = 'SHOES'
update ms_style set  upd_id = @gv_upd_id, size_group = 'F-SHOES'
where age_group = 'F' and size_group = 'SHOES'

update pur_size set size_seq = c.seq_no
from pur_dtl a, ms_style b, ms_sizeseq c
where pur_size.pur_id = a.pur_id and pur_size.seq_no = a.seq_no and 
	a.style_no = b.style_no and b.size_group = 'H-SHOES' and 
	b.size_group = c.size_grp and pur_size.size = c.size
  
update pur_size set size_seq = c.seq_no
from pur_dtl a, ms_style b, ms_sizeseq c
where pur_size.pur_id = a.pur_id and pur_size.seq_no = a.seq_no and 
	a.style_no = b.style_no and b.size_group = 'F-SHOES' and 
	b.size_group = c.size_grp and pur_size.size = c.size
rollback  tran
 /*
select b.pur_id, b.seq_no, d.seq_no  
from pur_dtl a, pur_size b, ms_style c, ms_sizeseq d
where a.pur_id = b.pur_id and a.style_no = c.style_no and 
a.seq_no = b.seq_no and c.size_group = 'SHOES' and 
age_group = 'F' and  d.size_grp = 'F-SHOES' and b.size = d.size
group by b.pur_id, b.seq_no, d.seq_no
having count(*) > 1
order by b.pur_id, b.seq_no, b.size*/
 